CREATE PROCEDURE [dbo].[asi_IsVatTaxableRegistration2]
    @eventCode varchar(10),
    @registrantCountry varchar(25),
    @registrantIsCompany bit,
    @registrantIsVatRegistered bit,
    @registrantVatCountry varchar(25)='',
    @applyVat bit = NULL OUT
AS
BEGIN

    -- declare variables    
    DECLARE @orgCode varchar(5)
    DECLARE @orgCountryCode varchar(2)
    DECLARE @registrantCountryCode varchar(2)
    DECLARE @ruleSet varchar(10)
    DECLARE @isEuCountry bit
    DECLARE @taxOnShipTo bit
    DECLARE @shipToVatCountryCode varchar(25)
     
    -- determine whether to use TaxOnShipTo option. defaults to false.
    SELECT @taxOnShipTo = CASE WHEN [ShortValue]='YES' THEN 1 ELSE 0 END
      FROM [dbo].[System_Params]
     WHERE [ParameterName] = 'AR_Control.TaxOnShipTo'  

    -- default the return value to true
    -- this effectively means, handle tax as you always did
    -- if we fail any validations we want the system to behave as it always did
    SET @applyVat = 1
    
    -- check we are licensed for VAT
    IF NOT EXISTS (SELECT 1
                     FROM [dbo].[LicenseLegacyList]
                    WHERE [LegacyLicenseCode]='VAT')
    BEGIN
        GOTO ReturnValue
    END

    -- verify the event exists, and retrieve some basic details
    IF LEN(ISNULL(@eventCode, ''))=0
    BEGIN
        GOTO ReturnValue
    END
    SELECT @orgCode = [ORG_CODE], @ruleSet = [VAT_RULESET]
      FROM [dbo].[Meet_Master]
     WHERE [MEETING] = @eventCode
    IF @@ROWCOUNT=0
    BEGIN
        GOTO ReturnValue
    END

    -- if the event has no vat ruleset assigned, we need go no further
    IF LEN(ISNULL(@ruleSet, ''))=0
    BEGIN
        GOTO ReturnValue
    END

    -- verify the event org code is configured for VAT, and that a VAT country has been specified for the org  
    -- if the event has no org code, assume the default org code
    IF LEN(ISNULL(@orgCode, ''))=0
    BEGIN
        SELECT @orgCode = o.[OrgCode], @orgCountryCode = c.[COUNTRY_CODE]
          FROM [dbo].[Org_Control] o
               INNER JOIN [dbo].[Country_Names] c on o.[VATCountry]=c.[VAT_COUNTRY_CODE]
         WHERE o.[DefaultFlag]=1 AND o.[UseVATTaxation]=1 AND LEN(o.[VATCountry])>0
    END
    ELSE
    BEGIN
        SELECT @orgCountryCode = c.[COUNTRY_CODE]
          FROM [dbo].[Org_Control] o
               INNER JOIN [dbo].[Country_Names] c on o.[VATCountry]=c.[VAT_COUNTRY_CODE]
         WHERE o.[OrgCode]=@orgCode AND o.[UseVATTaxation]=1 AND LEN(o.[VATCountry])>0
    END
    IF @@ROWCOUNT = 0
    BEGIN
        GOTO ReturnValue
    END
           
    -- verify the contact country exists
    -- if no country was supplied, establish the default
    SET @registrantCountryCode = NULL
    IF LEN(ISNULL(@registrantCountry, ''))=0
    BEGIN
        -- no country supplied, so assume the default
        -- use the VAT country code from the default org
        -- but only if the default org is configured for VAT
        SELECT @registrantCountryCode=c.[COUNTRY_CODE], @shipToVatCountryCode =  c.[VAT_COUNTRY_CODE]
          FROM [dbo].[Org_Control] o
               INNER JOIN [dbo].[Country_Names] c on o.[VATCountry]=c.[VAT_COUNTRY_CODE]
         WHERE o.[DefaultFlag]=1 AND o.[UseVATTaxation]=1 AND LEN(o.[VATCountry])>0
        -- if we still don't have a country code, assume US
        IF @@ROWCOUNT=0
        BEGIN
            SET @registrantCountryCode='US'        
            SET @shipToVatCountryCode=''
        END
    END
    ELSE
    BEGIN     
        -- check the supplied country exists    
        IF LEN(@registrantCountry)<=2
        BEGIN
            SELECT @registrantCountryCode = [COUNTRY_CODE], @shipToVatCountryCode =  [VAT_COUNTRY_CODE]  
              FROM [dbo].[Country_Names]
             WHERE [COUNTRY_CODE]=@registrantCountry
        END
        ELSE
        BEGIN
            SELECT @registrantCountryCode = [COUNTRY_CODE], @shipToVatCountryCode =  [VAT_COUNTRY_CODE]  
              FROM [dbo].[Country_Names]
             WHERE [COUNTRY]=@registrantCountry
        END
        IF @@ROWCOUNT=0
        BEGIN
            GOTO ReturnValue
        END
    END

    -- if taxing on ShipTo, then correct the @registrantIsVatRegistered flag, to ALSO check VatCountry
    IF @taxOnShipTo=1 AND @registrantIsVatRegistered = 1
    BEGIN
          SELECT @registrantIsVatRegistered = CASE WHEN LEN(ISNULL(@registrantVatCountry, ''))>0 AND @registrantVatCountry=@shipToVatCountryCode THEN 1 ELSE 0 END
    END
    
    -- now we have everything we need to start processing the rules
    
    -- determine whether the contact country is within the EU
    IF EXISTS (SELECT 1
                 FROM [dbo].[Country_Names]
                WHERE [COUNTRY_CODE]=@registrantCountryCode AND [MAIL_GROUP]='EU')
    BEGIN
        SET @isEuCountry = 1
    END
    ELSE
    BEGIN
        SET @isEuCountry = 0
    END
    
    -- process the ruleset
    SELECT TOP 1 @applyVat = [VAT_APPLIES]
      FROM [dbo].[Vat_Rule]
     WHERE [VAT_RULESET]=@ruleSet
           AND ([VAT_REGISTERED]=CASE @registrantIsVatRegistered WHEN 1 THEN 'Y' ELSE 'N' END OR [VAT_REGISTERED]='')
           AND ([COMPANY_CONTACT]=CASE @registrantIsCompany WHEN 1 THEN 'Y' ELSE 'N' END OR [COMPANY_CONTACT]='')
           AND (([LOCATION]='Same' AND @registrantCountryCode=@orgCountryCode)
                  OR
                ([LOCATION]='EU' AND @isEuCountry=1 AND @registrantCountryCode<>@orgCountryCode)
                  OR
                ([LOCATION]='NonEU' AND @isEuCountry=0)
                  OR
                [LOCATION]=''
               )
     ORDER BY [RULE_ORDER]
        
    IF @@ROWCOUNT = 0
    BEGIN
        SET @applyVat = 1
    END     

ReturnValue:    
    
    -- output parameter has already been set
    -- also return the value as a resultset for desktop
    SELECT @applyVat AS [ApplyVat]
    
END

GO